import {
  mysqlTable,
  mysqlSchema,
  AnyMySqlColumn,
  primaryKey,
  varchar,
  datetime,
  foreignKey,
  unique,
  index,
  decimal,
  int,
  tinyint,
} from 'drizzle-orm/mysql-core';
import { sql } from 'drizzle-orm';

export const accountGroup = mysqlTable(
  'account_group',
  {
    id: varchar({ length: 255 }).notNull(),
    title: varchar({ length: 50 }).notNull(),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    lastUpdated: datetime('last_updated', { mode: 'string', fsp: 6 }).notNull(),
  },
  (table) => [primaryKey({ columns: [table.id], name: 'account_group_id' })],
);

export const accountGroupUser = mysqlTable(
  'account_group_user',
  {
    id: varchar({ length: 255 }).notNull(),
    isAccountGroupCreator: tinyint('is_account_group_creator')
      .default(0)
      .notNull(),
    userId: varchar('user_id', { length: 255 })
      .notNull()
      .references(() => user.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    accountGroupId: varchar('account_group_id', { length: 255 })
      .notNull()
      .references(() => accountGroup.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
  },
  (table) => [
    primaryKey({ columns: [table.id], name: 'account_group_user_id' }),
    unique('UK49431f70e0388222b0953652060e').on(
      table.accountGroupId,
      table.userId,
    ),
  ],
);

export const appErrorReport = mysqlTable(
  'app_error_report',
  {
    id: varchar({ length: 255 }).notNull(),
    systemInfo: varchar('system_info', { length: 1000 }).notNull(),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    stackTrace: varchar('stack_trace', { length: 4000 }).notNull(),
    username: varchar({ length: 255 }),
    errorMessage: varchar('error_message', { length: 1000 }).notNull(),
    deviceInfo: varchar('device_info', { length: 1000 }),
  },
  (table) => [primaryKey({ columns: [table.id], name: 'app_error_report_id' })],
);

export const appReleaseLog = mysqlTable(
  'app_release_log',
  {
    id: varchar({ length: 255 }).notNull(),
    changeLog: varchar('change_log', { length: 255 }).notNull(),
    apkFilename: varchar('apk_filename', { length: 255 }).notNull(),
    appVersion: varchar('app_version', { length: 255 }).notNull(),
    releaseDate: datetime('release_date', { mode: 'string', fsp: 6 }).notNull(),
    appStoreUrl: varchar('app_store_url', { length: 255 }).notNull(),
    isMandatory: tinyint('is_mandatory').default(0).notNull(),
  },
  (table) => [primaryKey({ columns: [table.id], name: 'app_release_log_id' })],
);

export const bank = mysqlTable(
  'bank',
  {
    id: varchar({ length: 255 }).notNull(),
    shortName: varchar('short_name', { length: 50 }).notNull(),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    lastUpdated: datetime('last_updated', { mode: 'string', fsp: 6 }).notNull(),
    name: varchar({ length: 50 }).notNull(),
  },
  (table) => [primaryKey({ columns: [table.id], name: 'bank_id' })],
);

export const bankCard = mysqlTable(
  'bank_card',
  {
    id: varchar({ length: 255 }).notNull(),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    statementDate: varchar('statement_date', { length: 50 }),
    lastUpdated: datetime('last_updated', { mode: 'string', fsp: 6 }).notNull(),
    name: varchar({ length: 50 }).notNull(),
    accountGroupId: varchar('account_group_id', { length: 255 })
      .notNull()
      .references(() => accountGroup.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    bankId: varchar('bank_id', { length: 255 })
      .notNull()
      .references(() => bank.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    paymentDueDate: varchar('payment_due_date', { length: 50 }),
    customerServiceNumber: varchar('customer_service_number', { length: 50 }),
    isCreditCard: tinyint('is_credit_card').default(1).notNull(),
    enabled: tinyint().default(1).notNull(),
  },
  (table) => [primaryKey({ columns: [table.id], name: 'bank_card_id' })],
);

export const incomeExpenseDetail = mysqlTable(
  'income_expense_detail',
  {
    id: varchar({ length: 255 }).notNull(),
    bankCardId: varchar('bank_card_id', { length: 255 }).references(
      () => bankCard.id,
      { onDelete: 'restrict', onUpdate: 'restrict' },
    ),
    detailType: varchar('detail_type', { length: 255 }).notNull(),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    paymentType: varchar('payment_type', { length: 255 }),
    lastUpdated: datetime('last_updated', { mode: 'string', fsp: 6 }).notNull(),
    updatedBy: varchar('updated_by', { length: 255 }).notNull(),
    details: varchar({ length: 100 }).notNull(),
    type: varchar({ length: 255 }).notNull(),
    accountGroupId: varchar('account_group_id', { length: 255 })
      .notNull()
      .references(() => accountGroup.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    createdBy: varchar('created_by', { length: 255 }).notNull(),
    incomeExpenseDate: datetime('income_expense_date', {
      mode: 'string',
      fsp: 6,
    }).notNull(),
    remark: varchar({ length: 100 }),
    amount: decimal({ precision: 19, scale: 2 }).notNull(),
    province: varchar({ length: 255 }),
    district: varchar({ length: 255 }),
    formattedAddress: varchar('formatted_address', { length: 255 }),
    latitude: decimal({ precision: 19, scale: 6 }),
    address: varchar({ length: 255 }),
    longitude: decimal({ precision: 19, scale: 6 }),
    city: varchar({ length: 255 }),
  },
  (table) => [
    index('idx_income_expense_detail_detail_type').on(table.detailType),
    index('idx_income_expense_detail_income_expense_date').on(
      table.incomeExpenseDate,
    ),
    primaryKey({ columns: [table.id], name: 'income_expense_detail_id' }),
  ],
);

export const incomeExpenseDetailBaseType = mysqlTable(
  'income_expense_detail_base_type',
  {
    id: varchar({ length: 255 }).notNull(),
    title: varchar({ length: 50 }).notNull(),
    orderIndex: int('order_index').notNull(),
    incomeExpenseType: varchar('income_expense_type', { length: 50 }).notNull(),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    lastUpdated: datetime('last_updated', { mode: 'string', fsp: 6 }).notNull(),
    name: varchar({ length: 50 }).notNull(),
    analysisIncluded: tinyint('analysis_included').default(1).notNull(),
    subtitle: varchar({ length: 50 }),
  },
  (table) => [
    primaryKey({
      columns: [table.id],
      name: 'income_expense_detail_base_type_id',
    }),
  ],
);

export const incomeExpenseDetailType = mysqlTable(
  'income_expense_detail_type',
  {
    id: varchar({ length: 255 }).notNull(),
    title: varchar({ length: 50 }).notNull(),
    orderIndex: int('order_index').notNull(),
    incomeExpenseType: varchar('income_expense_type', { length: 50 }).notNull(),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    lastUpdated: datetime('last_updated', { mode: 'string', fsp: 6 }).notNull(),
    name: varchar({ length: 50 }).notNull(),
    analysisIncluded: tinyint('analysis_included').default(1).notNull(),
    accountGroupId: varchar('account_group_id', { length: 255 })
      .notNull()
      .references(() => accountGroup.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    subtitle: varchar({ length: 50 }),
  },
  (table) => [
    primaryKey({ columns: [table.id], name: 'income_expense_detail_type_id' }),
  ],
);

export const joinAccountGroupRequest = mysqlTable(
  'join_account_group_request',
  {
    id: varchar({ length: 255 }).notNull(),
    senderId: varchar('sender_id', { length: 255 })
      .notNull()
      .references(() => user.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    receiverId: varchar('receiver_id', { length: 255 })
      .notNull()
      .references(() => user.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    lastUpdated: datetime('last_updated', { mode: 'string', fsp: 6 }).notNull(),
    ownerId: varchar('owner_id', { length: 255 })
      .notNull()
      .references(() => user.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    accountGroupId: varchar('account_group_id', { length: 255 })
      .notNull()
      .references(() => accountGroup.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    roles: varchar({ length: 255 }).notNull(),
  },
  (table) => [
    primaryKey({ columns: [table.id], name: 'join_account_group_request_id' }),
  ],
);

export const journey = mysqlTable(
  'journey',
  {
    id: varchar({ length: 255 }).notNull(),
    startDate: datetime('start_date', { mode: 'string', fsp: 6 }).notNull(),
    regionId: varchar('region_id', { length: 255 })
      .notNull()
      .references(() => region.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    accountGroupId: varchar('account_group_id', { length: 255 })
      .notNull()
      .references(() => accountGroup.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    endDate: datetime('end_date', { mode: 'string', fsp: 6 }).notNull(),
  },
  (table) => [primaryKey({ columns: [table.id], name: 'journey_id' })],
);

export const journeyIncomeExpenseDetail = mysqlTable(
  'journey_income_expense_detail',
  {
    id: varchar({ length: 255 }).notNull(),
    bankCardId: varchar('bank_card_id', { length: 255 }).references(
      () => bankCard.id,
      { onDelete: 'restrict', onUpdate: 'restrict' },
    ),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    paymentType: varchar('payment_type', { length: 255 }),
    lastUpdated: datetime('last_updated', { mode: 'string', fsp: 6 }).notNull(),
    location: varchar({ length: 255 }).notNull(),
    updatedBy: varchar('updated_by', { length: 255 }).notNull(),
    details: varchar({ length: 100 }).notNull(),
    currency: varchar({ length: 255 }),
    type: varchar({ length: 255 }).notNull(),
    accountGroupId: varchar('account_group_id', { length: 255 })
      .notNull()
      .references(() => accountGroup.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    createdBy: varchar('created_by', { length: 255 }).notNull(),
    incomeExpenseDate: datetime('income_expense_date', {
      mode: 'string',
      fsp: 6,
    }).notNull(),
    remark: varchar({ length: 100 }),
    journeyId: varchar('journey_id', { length: 255 })
      .notNull()
      .references(() => journey.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    amount: decimal({ precision: 19, scale: 2 }).notNull(),
    province: varchar({ length: 255 }),
    district: varchar({ length: 255 }),
    formattedAddress: varchar('formatted_address', { length: 255 }),
    latitude: decimal({ precision: 19, scale: 6 }),
    address: varchar({ length: 255 }),
    longitude: decimal({ precision: 19, scale: 6 }),
    city: varchar({ length: 255 }),
  },
  (table) => [
    index('idx_journey_income_expense_detail_income_expense_date').on(
      table.incomeExpenseDate,
    ),
    primaryKey({
      columns: [table.id],
      name: 'journey_income_expense_detail_id',
    }),
  ],
);

export const region = mysqlTable(
  'region',
  {
    id: varchar({ length: 255 }).notNull(),
    title: varchar({ length: 50 }).notNull(),
    name: varchar({ length: 50 }).notNull(),
  },
  (table) => [primaryKey({ columns: [table.id], name: 'region_id' })],
);

export const requestMap = mysqlTable(
  'request_map',
  {
    id: varchar({ length: 255 }).notNull(),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    httpMethod: varchar('http_method', { length: 255 }),
    configAttribute: varchar('config_attribute', { length: 255 }).notNull(),
    lastUpdated: datetime('last_updated', { mode: 'string', fsp: 6 }).notNull(),
    url: varchar({ length: 255 }).notNull(),
  },
  (table) => [
    primaryKey({ columns: [table.id], name: 'request_map_id' }),
    unique('UKd27576f6f7e718e71d707d8c47a1').on(table.httpMethod, table.url),
  ],
);

export const role = mysqlTable(
  'role',
  {
    id: varchar({ length: 255 }).notNull(),
    title: varchar({ length: 50 }).notNull(),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    authority: varchar({ length: 50 }).notNull(),
    description: varchar({ length: 50 }),
  },
  (table) => [
    primaryKey({ columns: [table.id], name: 'role_id' }),
    unique('UK_rd7q9jehhtr53mwmnvso7qqyx').on(table.authority),
  ],
);

export const user = mysqlTable(
  'user',
  {
    id: varchar({ length: 255 }).notNull(),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    passwordExpired: tinyint('password_expired').default(0).notNull(),
    lastUpdated: datetime('last_updated', { mode: 'string', fsp: 6 }).notNull(),
    gender: int(),
    accountExpired: tinyint('account_expired').default(0).notNull(),
    fullName: varchar('full_name', { length: 50 }).notNull(),
    authFailedAttempt: int('auth_failed_attempt').notNull(),
    username: varchar({ length: 50 }).notNull(),
    accountLocked: tinyint('account_locked').default(0).notNull(),
    password: varchar({ length: 100 }).notNull(),
    accountGroupId: varchar('account_group_id', { length: 255 }).references(
      () => accountGroup.id,
      { onDelete: 'restrict', onUpdate: 'restrict' },
    ),
    enabled: tinyint().default(1).notNull(),
    description: varchar({ length: 200 }),
    avatarFilename: varchar('avatar_filename', { length: 200 }),
  },
  (table) => [
    primaryKey({ columns: [table.id], name: 'user_id' }),
    unique('UK_spx0dw50t0vogfi1b7lk98e2r').on(table.username),
  ],
);

export const userActiveToken = mysqlTable(
  'user_active_token',
  {
    id: varchar({ length: 255 }).notNull(),
    expiration: int().notNull(),
    dateCreated: datetime('date_created', { mode: 'string', fsp: 6 }).notNull(),
    dateGenerated: datetime('date_generated', {
      mode: 'string',
      fsp: 6,
    }).notNull(),
    username: varchar({ length: 50 }).notNull(),
    lastUpdated: datetime('last_updated', { mode: 'string', fsp: 6 }).notNull(),
    accessToken: varchar('access_token', { length: 3000 }).notNull(),
    refreshToken: varchar('refresh_token', { length: 3000 }).notNull(),
    dateExpired: datetime('date_expired', { mode: 'string', fsp: 6 }).notNull(),
    userAgent: varchar('user_agent', { length: 200 }),
    deviceId: varchar('device_id', { length: 200 }),
    enabled: tinyint().default(1).notNull(),
    deviceSystemVersion: varchar('device_system_version', { length: 100 }),
    deviceProductName: varchar('device_product_name', { length: 100 }),
    deviceSystemName: varchar('device_system_name', { length: 100 }),
    deviceModel: varchar('device_model', { length: 100 }),
    deviceBrand: varchar('device_brand', { length: 100 }),
    appVersion: varchar('app_version', { length: 100 }),
    operatingSystemVersion: varchar('operating_system_version', {
      length: 100,
    }),
    platformVersion: varchar('platform_version', { length: 100 }),
    operatingSystem: varchar('operating_system', { length: 100 }),
    ipAddress: varchar('ip_address', { length: 255 }).notNull(),
    accountGroupId: varchar('account_group_id', { length: 255 }).notNull(),
  },
  (table) => [
    primaryKey({ columns: [table.id], name: 'user_active_token_id' }),
  ],
);

export const userRole = mysqlTable(
  'user_role',
  {
    id: varchar({ length: 255 }).notNull(),
    roleId: varchar('role_id', { length: 255 })
      .notNull()
      .references(() => role.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    userId: varchar('user_id', { length: 255 })
      .notNull()
      .references(() => user.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
    accountGroupId: varchar('account_group_id', { length: 255 })
      .notNull()
      .references(() => accountGroup.id, {
        onDelete: 'restrict',
        onUpdate: 'restrict',
      }),
  },
  (table) => [
    primaryKey({ columns: [table.id], name: 'user_role_id' }),
    unique('UK49ad9a5625dc55a282f234802c2b').on(
      table.userId,
      table.accountGroupId,
      table.roleId,
    ),
  ],
);
